using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class VENDORDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procVENDOR_add]";
        private string _strSPUpdateName = "dbo.[procVENDOR_update]";
        private string _strSPDeleteName = "dbo.[procVENDOR_delete]";
        private string _strSPGetName = "dbo.[procVENDOR_get]";
        private string _strSPGetAllName = "dbo.[procVENDOR_getall]";
        private string _strSPGetPages = "dbo.[procVENDOR_getpaged]";
        private string _strSPIsExist = "dbo.[procVENDOR_isexist]";
        private string _strSPIsExistName = "dbo.[procVENDORName_isexist]";
        private string _strSPIsExistNameCode = "dbo.[procVENDORNameCode_isexist]";
        private string _strTableName = "VENDOR";

        private string _strSPInsertName1 = "dbo.[procVENDOR1_add]";
        private string _strSPUpdateName1 = "dbo.[procVENDOR1_update]";
        private string _strSPDeleteName1 = "dbo.[procVENDOR1_delete]";
        private string _strSPGetName1 = "dbo.[procVENDOR1_get]";
        private string _strSPGetAllName1 = "dbo.[procVENDOR1_getall]";
        private string _strSPGetPages1 = "dbo.[procVENDOR1_getpaged]";
        private string _strSPIsExist1 = "dbo.[procVENDOR1_isexist]";


        #endregion Local Variable

        #region Method
        public VENDORInfo Get_VENDOR(
            String VendorCode
        , ref string sErr)
        {
            VENDORInfo objEntr = new VENDORInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("VendorCode", VendorCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = new VENDORInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }


        public DataTable GetAll_VENDOR(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_VENDOR(VENDORInfo objEntr, ref string sErr)
        {
            if (IsExist_VENDOR(objEntr.VendorCode))
            {
                sErr = objEntr.VendorCode + " is already existed, please check!";
                return -1;
            }
            int ret = -1;
            openConnection();
            InitSPCommand(_strSPInsertName);
            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            AddParameter("Beneficiary", objEntr.Beneficiary);
            AddParameter("BankName", objEntr.BankName);
            AddParameter("AccountNo1", objEntr.AccountNo1);
            AddParameter("AccountNo2", objEntr.AccountNo2);
            AddParameter("SwiftCode", objEntr.SwiftCode);
            AddParameter("VATCode", objEntr.VATCode);
            AddParameter("Address", objEntr.Address);
            AddParameter("Fax_Tel", objEntr.Fax_Tel);
            AddParameter("Email", objEntr.Email);
            AddParameter("ContactPerson", objEntr.ContactPerson);
            AddParameter("Note1", objEntr.Note1);
            AddParameter("Note2", objEntr.Note2);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            AddParameter("BankAddress", objEntr.BankAddress);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_VENDOR(VENDORInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            AddParameter("Beneficiary", objEntr.Beneficiary);
            AddParameter("BankName", objEntr.BankName);
            AddParameter("AccountNo1", objEntr.AccountNo1);
            AddParameter("AccountNo2", objEntr.AccountNo2);
            AddParameter("SwiftCode", objEntr.SwiftCode);
            AddParameter("VATCode", objEntr.VATCode);
            AddParameter("Address", objEntr.Address);
            AddParameter("Fax_Tel", objEntr.Fax_Tel);
            AddParameter("Email", objEntr.Email);
            AddParameter("ContactPerson", objEntr.ContactPerson);
            AddParameter("Note1", objEntr.Note1);
            AddParameter("Note2", objEntr.Note2);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            AddParameter("BankAddress", objEntr.BankAddress);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_VENDOR(
            String VendorCode
        )
        {
            openConnection();
            InitSPCommand(_strSPDeleteName);
            AddParameter("VendorCode", VendorCode);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist_VENDOR(
            String VendorCode
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("VendorCode", VendorCode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        public Boolean IsExist_VENDORName(
            String VendorName
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistName);

            AddParameter("VendorName", VendorName);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
        public Boolean IsExist_VENDORNameCode(
            String VendorCode, String VendorName
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExistNameCode);

            AddParameter("VendorCode", VendorCode);
            AddParameter("VendorName", VendorName);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(VENDORInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public VENDORInfo Get_VENDOR1(
            String VendorCode
        , ref string sErr)
        {
            VENDORInfo objEntr = new VENDORInfo();
            InitConnect();
            InitCommand(@"SELECT     VendorCode, VendorName, Beneficiary, BankName, AccountNo1, AccountNo2, SwiftCode, VATCode, Address, Fax_Tel, Email, ContactPerson, Note1, Note2, 
                      PaymentTerm, BankAddress
                FROM         VENDOR1
                WHERE     (VendorCode = @VendorCode) ", CommandType.Text);

            AddParameter("VendorCode", VendorCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = new VENDORInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }



        public DataTable GetAll_VENDOR1(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName1);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_VENDOR1(VENDORInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO VENDOR1(VendorCode, VendorName, Beneficiary, BankName, AccountNo1, AccountNo2, SwiftCode, VATCode, Address, Fax_Tel, Email, ContactPerson, Note1, Note2, PaymentTerm, BankAddress)
                VALUES     (@VendorCode,@VendorName,@Beneficiary,@BankName,@AccountNo1,@AccountNo2,@SwiftCode,@VATCode,@Address,@Fax_Tel,@Email,@ContactPerson,@Note1,@Note2,@PaymentTerm,@BankAddress)", CommandType.Text);

            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            AddParameter("Beneficiary", objEntr.Beneficiary);
            AddParameter("BankName", objEntr.BankName);
            AddParameter("AccountNo1", objEntr.AccountNo1);
            AddParameter("AccountNo2", objEntr.AccountNo2);
            AddParameter("SwiftCode", objEntr.SwiftCode);
            AddParameter("VATCode", objEntr.VATCode);
            AddParameter("Address", objEntr.Address);
            AddParameter("Fax_Tel", objEntr.Fax_Tel);
            AddParameter("Email", objEntr.Email);
            AddParameter("ContactPerson", objEntr.ContactPerson);
            AddParameter("Note1", objEntr.Note1);
            AddParameter("Note2", objEntr.Note2);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            AddParameter("BankAddress", objEntr.BankAddress);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_VENDOR1(VENDORInfo objEntr)
        {

            openConnection();
            InitCommand(@"UPDATE    VENDOR1
                SET     Beneficiary = @Beneficiary, BankName = @BankName, AccountNo1 = @AccountNo1, AccountNo2 = @AccountNo2, SwiftCode = @SwiftCode, VATCode = @VATCode, 
                        Address = @Address, Fax_Tel = @Fax_Tel, Email = @Email, ContactPerson = @ContactPerson, Note1 = @Note1, Note2 = @Note2, PaymentTerm = @PaymentTerm, 
                        BankAddress = @BankAddress, VendorName = @VendorName
                WHERE     (VendorCode = @VendorCode)", CommandType.Text);

            AddParameter("VendorCode", objEntr.VendorCode);
            AddParameter("VendorName", objEntr.VendorName);
            AddParameter("Beneficiary", objEntr.Beneficiary);
            AddParameter("BankName", objEntr.BankName);
            AddParameter("AccountNo1", objEntr.AccountNo1);
            AddParameter("AccountNo2", objEntr.AccountNo2);
            AddParameter("SwiftCode", objEntr.SwiftCode);
            AddParameter("VATCode", objEntr.VATCode);
            AddParameter("Address", objEntr.Address);
            AddParameter("Fax_Tel", objEntr.Fax_Tel);
            AddParameter("Email", objEntr.Email);
            AddParameter("ContactPerson", objEntr.ContactPerson);
            AddParameter("Note1", objEntr.Note1);
            AddParameter("Note2", objEntr.Note2);
            AddParameter("PaymentTerm", objEntr.PaymentTerm);
            AddParameter("BankAddress", objEntr.BankAddress);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_VENDOR1(
            String VendorCode
        )
        {
            openConnection();
            InitCommand(@"DELETE FROM   VENDOR1
                WHERE VendorCode = @VendorCode", CommandType.Text);

            AddParameter("VendorCode", VendorCode);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }       

        public Boolean IsExist_VENDOR1(
            String VendorCode
        )
        {
            InitConnect();
            InitCommand(@"SELECT  TOP 1   VendorCode
                FROM         VENDOR1
                WHERE VendorCode = @VendorCode", CommandType.Text);

            AddParameter("VendorCode", VendorCode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
        #endregion Method


        public bool IsExist_VENDOR1Name(string name)
        {
            InitConnect();
            InitCommand(@"SELECT  TOP 1   VendorName
                FROM         VENDOR1
                WHERE VendorName = @VendorName", CommandType.Text);

            AddParameter("VendorName", name);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }
    }
}
